﻿/*-----------------------------------------------------------------------------!
! DBSadharon class                                                             !
!------------------------------------------------------------------------------!
! System   : Inventory Management System                                       !
! Client   : UK based                                                          !
! Date     : April 2011                                                        !
! Copyright: (C) KAWISOFT Inc                                                  !
! Program  : DBSadharon.cs                                                     !
! Function : common database related methods                                   !
!------------------------------------------------------------------------------!
! Notes:                                                                       !
! Original author: Md. Shaiful Islam                                           !
! Last changed by: Md. Shaiful Islam, May 02 2011                              !
===============================================================================*/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace Model.Sadharon
{
    public class DBSadharon
    {

        /**
         * getUniqId
         * @parma conn sql connection
         * @param tableName e.g. Supplier/Customer
         * @param preFix e.g. SP/CU
         * @return uniqueId
         **/
        public string getUniqId(SqlConnection conn, string tableName, string preFix)
        {
            string uniqueId = "";
            string currentValue = "";
            string incrValue = "";
            int increment = 1;
            int current = 0;
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "Select * from TablesId where SequenceName='" + tableName + "'";
                cmd.Connection = conn;
                conn.Open();
                SqlDataReader sdr = cmd.ExecuteReader();
                if (sdr.Read())
                {
                    currentValue = sdr["CurrentValue"].ToString();
                    incrValue = sdr["Increment"].ToString();

                    increment = int.Parse(incrValue);
                    current = int.Parse(currentValue);

                    uniqueId = preFix + currentValue;
                    current = current + increment;
                }
                closeConnection(conn);

                // update current value
                if (current > 0)
                {
                    string query = "Update TablesId Set CurrentValue='" + current.ToString() + "' Where SequenceName='" + tableName + "'";
                    SqlCommand cmdUpdt = new SqlCommand();
                    cmdUpdt.CommandType = CommandType.Text;
                    cmdUpdt.CommandText = query;
                    cmdUpdt.Connection = conn;
                    conn.Open();
                    if (cmdUpdt.ExecuteNonQuery() < 1)
                    {
                        uniqueId = "failed";
                    }
                }

            }
            catch (SqlException e)
            {
                uniqueId = "failed";
            }
            catch (Exception ex)
            {
                uniqueId = "failed";
            }
            finally
            {
                closeConnection(conn);
            }
            return uniqueId;
        }


        /**
         * closeConnection : closes the sql connection
         * @param conn sql connection
         **/
        public static void closeConnection(SqlConnection conn)
        {
            if (conn != null)
            {
                try
                {
                    conn.Close();
                }
                catch (Exception e) { }
            }
        }

        /**
         * MakeQuery
         * @param queryString : query string
         * #param conn : sql connection
         **/
        public static SqlCommand MakeQuery(string queryString, SqlConnection conn)
        {
            return (new SqlCommand(queryString, conn));
        }


    }
}
